﻿using System;
using System.Data;
using System.Data.SQLite;

namespace XD.Core
{
    /// <summary>
    /// Sqlite数据库操作类
    /// </summary>
    public class SqliteDB : IDisposable
    {
        #region 字段

        /// <summary>
        /// 数据库连接字符串构造器
        /// </summary>
        private SQLiteConnectionStringBuilder _connStr = null;

        /// <summary>
        /// 数据库连接器
        /// </summary>
        private SQLiteConnection _conn = null;

        /// <summary>
        /// 通过连接器创建NpgsqlCommand对象，用于执行SQL语句
        /// </summary>
        private SQLiteCommand _comm = null;

        /// <summary>
        /// 事务管理对象
        /// </summary>
        private SQLiteTransaction _trans = null;

        #endregion

        #region 构造函数

        public SqliteDB(string filePath)
        {
            _connStr = new SQLiteConnectionStringBuilder();
            _connStr.DataSource = filePath;
            _conn = new SQLiteConnection(_connStr.ToString());
            _comm = _conn.CreateCommand();
        }

        ~SqliteDB()
        {
        }

        #endregion

        #region 静态方法

        /// <summary>
        /// 创建数据库
        /// </summary>
        /// <param name="filePath">db文件路径+文件名</param>
        public static void CreateDB(string filePath)
        {
            try
            {
                SQLiteConnection.CreateFile(filePath);
            }
            catch
            {
                throw;
            }
        }

        #endregion

        #region 公共方法

        #region 数据库操作：连接数据库、断开数据库、加密数据库

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <returns>是否连接成功</returns>
        public bool Open()
        {
            try
            {
                _conn.Open();
                return true;
            }
            catch
            {
                Close();
                return false;
                throw;
            }
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <param name="password">密码</param>
        /// <returns>是否连接成功</returns>
        public bool Open(string password)
        {
            try
            {
                _conn.SetPassword(password);
                _conn.Open();
                return true;
            }
            catch
            {
                Close();
                return false;
                throw;
            }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void Close()
        {
            if (_conn != null)
            {
                _comm.Dispose();
                _conn.Close();
                _conn.Dispose();
            }
        }

        /// <summary>
        /// 给sqlite数据库加密
        /// </summary>
        /// <param name="password">密码</param>
        public void Encrypt(string password)
        {
            try
            {
                if (_conn != null)
                {
                    _conn.ChangePassword(password);
                }
            }
            catch
            {
                throw;
            }
        }

        #endregion

        #region 查询

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable ExecuteSQL(string sql)
        {
            try
            {
                _comm.Transaction = _trans;
                _comm.CommandText = sql;
                DataTable dt = new DataTable();
                using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(_comm))
                {
                    adapter.Fill(dt);
                }
                return dt;
            }
            catch
            {
                throw;
            }
        }

        #endregion

        #region 事务管理

        /// <summary>
        /// 开始执行事务
        /// </summary>
        public void BeginTransaction()
        {
            if (_conn != null)
            {
                _trans = _conn.BeginTransaction();
            }
        }

        /// <summary>
        /// 事务结束，保存并提交数据库数据，
        /// </summary>
        public void Commit()
        {
            if (_trans != null)
            {
                _trans.Commit();
            }
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        public void Rollback()
        {
            if (_trans != null)
            {
                _trans.Rollback();
            }
        }

        /// <summary>
        /// 结束事务，并释放资源
        /// </summary>
        public void EndTransaction()
        {
            if (_trans != null)
            {
                _trans.Dispose();
                _trans = null;
            }
        }

        #endregion

        #endregion

        #region IDisposable 成员

        public void Dispose()
        {
            this.Close();
        }

        #endregion
    }
}
